package dao;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import user.Student;
import util.DruidUtils;

import java.sql.SQLException;
import java.util.List;

public class StudentDaoImp implements StudentDao {


    @Override
    public List<Student> getStudent(String  start,String  end) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="select * from homework.student ORDER BY num ASC LIMIT ?,?";
        int i = Integer.parseInt(start);
        int i1 = Integer.parseInt(end);
        Object[] params = {i*i1,i1};
        return qr.query(sql,new BeanListHandler<Student>(Student.class),params);
    }

    @Override
    public int addStudent(Student student) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="INSERT INTO homework.student values(?,?,?,?,?,?)";
        Object[] params = {student.getNum(),student.getName(),student.getSex(),student.getBirthday(),student.getEmail(),student.getRemark()};
        return qr.update(sql,params);
    }

    @Override
    public int deleteStudent(String num) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="delete from homework.student where num= ? ";
        return qr.update(sql,num);
    }

    @Override
    public int motifyStudent(Student student) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="update homework.student SET `name`=?,`sex`=?,`birthday`=?,`email`=?,`remark`=? where `num`=?";
        Object[] params = {student.getName(),student.getSex(),student.getBirthday(),student.getEmail(),student.getRemark(),student.getNum()};
        return qr.update(sql,params);
    }

    @Override
    public Student findStudentById(String num) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="select * from homework.student where num=?";
        return qr.query(sql,new BeanHandler<Student>(Student.class),num);
    }

    @Override
    public Student findStudentByName(String name) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="select * from homework.student where name=?";
        return qr.query(sql,new BeanHandler<Student>(Student.class),name);
    }

    @Override
    public int countStudent() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        String sql ="select count(num) FROM homework.student";
        return ((Long) qr.query(sql, new ScalarHandler())).intValue();
    }
}
